Imports System.Data.OleDb

''' <summary>
''' Base class for all OleDB providers (Access, Paradox, dBase, Excel, Text)
''' </summary>
''' <remarks></remarks>
Public Class clsDBOleDB
    Inherits clsDB

    ''' <summary>
    ''' Return a copy of the current generic connection
    ''' </summary>
    ''' <returns>Clone of current instance</returns>
    Public Overrides Function Clone() As clsDB
        Dim db As clsDB = New clsDBOleDB
        MyBase.CloneSelf(db)
        Return db
    End Function

    Public Overrides Function CommandBuilder(ByVal da As System.Data.Common.DbDataAdapter) As System.Data.Common.DbCommandBuilder
        Return New OleDbCommandBuilder(da)
    End Function

    Public Overrides Function DataAdapter(ByVal SqlString As String) As System.Data.Common.DbDataAdapter
        If Connection Is Nothing Then Return Nothing
        Return New OleDbDataAdapter(SqlString, CType(Connection, OleDbConnection))
    End Function

    Public Overrides Function Parameter(ByRef Command As DbCommand, ByVal ParameterName As String, ByVal DataType As System.Type, Optional ByVal Size As Short = 0) As DbParameter
        If Connection Is Nothing Then Return Nothing
        Dim param As OleDbParameter
        Dim OleType As OleDbType
        If DataType Is GetType(String) Then
            OleType = OleDbType.VarChar
        ElseIf DataType Is GetType(Date) Then
            OleType = OleDbType.Date
        ElseIf DataType Is GetType(Double) Then
            OleType = OleDbType.Double
        ElseIf DataType Is GetType(Integer) Then
            OleType = OleDbType.Integer
        ElseIf DataType Is GetType(Boolean) Then
            OleType = OleDbType.Boolean
        ElseIf DataType Is GetType(Short) Then
            OleType = OleDbType.SmallInt
        Else
            Throw New ApplicationException("Invalid type passed to Parameter.")
            Return Nothing
        End If
        If Size > 0 Then
            'param = New OleDbParameter(ParameterName, OleType, Size)
            'param.Value = ""
            param = Command.CreateParameter
            With param
                .ParameterName = ParameterName
                .OleDbType = OleType
                If Size > 0 Then .Size = Size
                .Value = DBNull.Value
            End With
        Else
            param = New OleDbParameter(ParameterName, OleType)
        End If
        param.Value = DBNull.Value
        Return param
    End Function

    Public Overrides Sub CreateConnection()
        'this must be overridden by inheritors
    End Sub

End Class

''' <summary>
''' dBase III provider; not placed in seperate module because will not use as supported data type
''' However, will use internally
''' Note that this is not a supported data type because of some severe limitations in the driver:
''' Field names less than or equal to 10 characters (violated by RCodes and Branches) and primary key limitations; 
''' also, TIMESTAMP fields are not supported so only see date, not time; tried to implement ODBC in hopes of getting
''' that feature, but it also doesn't support it.
''' </summary>
Public Class clsDBDBase
    Inherits clsDBOleDB

    ''' <summary>
    ''' Creates a provider-specific connection object given previously specified properties as required (DataSource, DatabaseName, UserName, Password, etc.)
    ''' Note that the connection is normally automatically created when you instantiate the class
    ''' </summary>
    Public Overrides Sub CreateConnection()
        Dim csb As New OleDbConnectionStringBuilder
        With csb
            .Provider = "Microsoft.Jet.OLEDB.4.0"
            .Item("Extended Properties") = "DBase IV"
            .DataSource = DataSource 'should be directory
            Connection = New OleDbConnection(.ConnectionString)
        End With
    End Sub

End Class

''' <summary>
''' Excel provider; not placed in seperate module because will not use as supported data type
''' However, will use internally for fast export to Excel (with no formatting)
''' </summary>
Public Class clsDBExcel
    Inherits clsDBOleDB

    ''' <summary>
    ''' Creates a provider-specific connection object given previously specified properties as required (DataSource, DatabaseName, UserName, Password, etc.)
    ''' Note that the connection is normally automatically created when you instantiate the class
    ''' </summary>
    Public Overrides Sub CreateConnection()
        If Connection IsNot Nothing Then Connection.Close() : Connection.Dispose()
        Dim csb As New OleDbConnectionStringBuilder
        With csb

            'note: due to bug in driver, cannot force Excel to scan more than 8 rows; only workaround according to http://support.microsoft.com/kb/189897 is
            'to set the registry using regedit (cannot do programmatically)

            If DataSource.EndsWith(".xls", StringComparison.OrdinalIgnoreCase) Then
                .Provider = "Microsoft.Jet.OLEDB.4.0"
                .Item("Extended Properties") = "Excel 8.0;HDR=Yes" 'cannot user IMEX=1--prevents me from creating new workbooks or updating existing ones
            Else
                .Provider = "Microsoft.ACE.OLEDB.12.0"
                .Item("Extended Properties") = "Excel 12.0 Xml;HDR=Yes"
            End If
            .DataSource = DataSource 'should be path and xls filename (note: table names should be formatted like [Sheet1$]
            Connection = New OleDbConnection(.ConnectionString)
        End With
    End Sub

    Public Overrides Function TableExists(ByVal TableName As String) As Boolean
        If Not My.Computer.FileSystem.FileExists(DataSource) Then Return False
        Return MyBase.TableExists(TableName)
    End Function

    Public Overrides Function GetTableList() As System.Collections.Generic.List(Of String)
        'for some reason, returns sheet name and sheet name with $ appended (twice)
        Dim lst As Generic.List(Of String) = MyBase.GetTableList()
        For i As Integer = lst.Count - 1 To 0 Step -1
            If lst(i).EndsWith("$") Then lst.RemoveAt(i)
        Next
        Return lst
    End Function

    Public Overrides Function GetReservedWords() As System.Collections.Generic.List(Of String)
        Return New Generic.List(Of String)
    End Function
End Class

''' <summary>
''' Text provider; not placed in seperate module because will not use as supported data type
''' However, will use internally (well, maybe not--my text import/export is already very fast!)
''' </summary>
Public Class clsDBText
    Inherits clsDBOleDB

    ''' <summary>
    ''' Creates a provider-specific connection object given previously specified properties as required (DataSource, DatabaseName, UserName, Password, etc.)
    ''' Note that the connection is normally automatically created when you instantiate the class
    ''' </summary>
    Public Overrides Sub CreateConnection()
        Dim csb As New OleDbConnectionStringBuilder
        With csb
            .Provider = "Microsoft.Jet.OLEDB.4.0"
            .Item("Extended Properties") = "Text;HDR=YES;FMT=Delimited"
            .DataSource = DataSource 'should be path and txt filename (note: table names should be formatted like [filename#txt]
            Connection = New OleDbConnection(.ConnectionString)
        End With
    End Sub

    ''' <summary>
    ''' Return a list of columns for this table (all fields are converted to UPPERCASE)
    ''' </summary>
    ''' <param name="TableName">Name of table</param> 
    ''' <returns></returns>
    ''' <remarks>This MUST be called to create the SCHEMA.INI file for proper reading of the table</remarks>
    Public Overrides Function GetColumnList(ByVal TableName As String) As Generic.List(Of String)
        Dim FileName As String = TableName.Replace("#", ".").Replace("[", "").Replace("]", "")
        If Not FileName.EndsWith(".txt", StringComparison.InvariantCultureIgnoreCase) Then FileName &= ".txt"
        Dim sr As New IO.StreamReader(MyBase.DataSource & "\" & FileName)
        Try
            MyBase.Connection.Close()
            Dim col As New Generic.List(Of String)
            If Not sr.EndOfStream Then
                col.AddRange(sr.ReadLine.ToUpper.Split(vbTab))

                'want to treat each field as text; update schema.ini file and then reopen
                'if TXT or CSV, may need to set field names and types for each column
                'this is because, may read the first few lines, see only numbers in the
                'station_id column and conclude to use an integer type; later on, when
                'a string is encountered, the table sets it to null
                'therefore, write to schema.ini the column name and type (I guess it overrides the row headings)

                Dim ini As New WRDB.IniFile.clsIniFile(DataSource & "\schema.ini", True)
                ini.SetKeyText(FileName, "Format", "TabDelimited")
                ini.SetKeyText(FileName, "ColNameHeader", "True")
                For i As Integer = 0 To col.Count - 1
                    Dim coltype As String = "Text"

                    'this has been commented out because otherwise cannot interpret standard date format 1938-01-01 00:00--BUT IS NEEDED FOR PARADOX!

                    If col(i).ToUpper.Contains("DATE") Then
                        coltype = "Date"
                    End If
                    ini.SetKeyText(FileName, "Col" & i + 1, col(i) & " " & coltype)
                Next
                ini.Save()

            End If
            Return col
        Catch ex As Exception
            Return New Generic.List(Of String) 'return empty list to avoid later errors
        Finally
            sr.Close()
            sr.Dispose()
        End Try
    End Function

End Class

''' <summary>
''' Text provider; not placed in seperate module because will not use as supported data type
''' However, will use internally (well, maybe not--my text import/export is already very fast!)
''' </summary>
Public Class clsDBCsv
    Inherits clsDBOleDB

    ''' <summary>
    ''' Creates a provider-specific connection object given previously specified properties as required (DataSource, DatabaseName, UserName, Password, etc.)
    ''' Note that the connection is normally automatically created when you instantiate the class
    ''' </summary>
    Public Overrides Sub CreateConnection()
        Dim csb As New OleDbConnectionStringBuilder
        With csb
            .Provider = "Microsoft.Jet.OLEDB.4.0"
            .Item("Extended Properties") = "Text;HDR=YES;FMT=Delimited"
            .DataSource = DataSource 'should be path and xls filename (note: table names should be formatted like [Sheet1$]
            Connection = New OleDbConnection(.ConnectionString)
        End With
    End Sub

    ''' <summary>
    ''' Return a list of columns for this table (all fields are converted to UPPERCASE)
    ''' </summary>
    ''' <param name="TableName">Name of table</param> 
    ''' <returns></returns>
    ''' <remarks>This MUST be called to create the SCHEMA.INI file for proper reading of the table</remarks>
    Public Overrides Function GetColumnList(ByVal TableName As String) As Generic.List(Of String)
        Dim FileName As String = TableName.Replace("#", ".").Replace("[", "").Replace("]", "")
        Dim sr As New IO.StreamReader(MyBase.DataSource & "\" & FileName)
        Try
            MyBase.Connection.Close()
            Dim col As New Generic.List(Of String)
            If Not sr.EndOfStream Then
                col.AddRange(sr.ReadLine.ToUpper.Split(","))

                'want to treat each field as text; update schema.ini file and then reopen
                'if TXT or CSV, may need to set field names and types for each column
                'this is because, may read the first few lines, see only numbers in the
                'station_id column and conclude to use an integer type; later on, when
                'a string is encountered, the table sets it to null
                'therefore, write to schema.ini the column name and type (I guess it overrides the row headings)

                Dim ini As New WRDB.IniFile.clsIniFile(DataSource & "\schema.ini", True)
                ini.SetKeyText(FileName, "Format", "CSVDelimited")
                ini.SetKeyText(FileName, "ColNameHeader", "True")
                For i As Integer = 0 To col.Count - 1
                    Dim coltype As String = "Text"
                    If col(i).ToUpper.Contains("DATE") Then
                        coltype = "Date"
                    End If
                    ini.SetKeyText(FileName, "Col" & i + 1, col(i) & " " & coltype)
                Next
                ini.Save()

            End If
            Return col
        Catch ex As Exception
            Return New Generic.List(Of String) 'return empty list to avoid later errors
        Finally
            sr.Close()
            sr.Dispose()
        End Try
    End Function

End Class
